package com.swak.persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.swak.define.CurdDefine;
import com.swak.entity.Page;
import com.swak.entity.Parameters;
import com.swak.persistence.dialect.Dialect;
import com.swak.utils.Lists;
import com.swak.utils.Maps;
import com.swak.utils.StringUtils;

/**
 * 基于数据库的存储
 * 
 * @author lifeng
 * @date 2021年2月3日 下午2:13:03
 */
public abstract class DatabaseStorage {

	protected static Logger logger = LoggerFactory.getLogger(DatabaseStorage.class);

	protected DataSource dataSource;

	public DatabaseStorage(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	/**
	 * 执行多条语句
	 * 
	 * @return
	 * @throws SQLException
	 */
	protected void executeMultiSql(Consumer<Connection> consumer) {
		Connection connection = null;
		try {
			connection = dataSource.getConnection();
			connection.setAutoCommit(false);
			consumer.accept(connection);
			connection.commit();
		} catch (SQLException e) {
			this.error(e);
			try {
				connection.rollback();
			} catch (SQLException ex) {
			}
		} finally {
			close(connection, null, null);
		}
	}

	/**
	 * 执行更新
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	protected int executeMultiSql_Update(final Connection connection, final String sql, final Object... params) {
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			return ps.executeUpdate();
		} catch (SQLException e) {
			this.error("Execute Sql[{}] Error: {}", sql, e);
			throw new RuntimeException(e);
		} finally {
			close(null, ps, null);
		}
	}

	/**
	 * 执行查询
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	protected List<Map<String, Object>> executeMultiSql_Query(final Connection connection, final String sql,
			final Object... params) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Map<String, Object>> list = null;
		try {
			ps = connection.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			rs = ps.executeQuery();
			ResultSetMetaData md = rs.getMetaData();
			int columnCount = md.getColumnCount();
			list = new ArrayList<>();
			while (rs.next()) {
				Map<String, Object> rowData = Maps.newHashMap();
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(md.getColumnName(i), rs.getObject(i));
				}
				list.add(rowData);
			}
		} catch (SQLException e) {
			this.error("Execute Sql[{}] Error: {}", sql, e);
			throw new RuntimeException(e);
		} finally {
			close(null, ps, rs);
		}
		return list;
	}

	/**
	 * 执行更新
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	protected int executeUpdate(final String sql, final Object... params) {
		Connection connection = null;
		PreparedStatement ps = null;
		try {
			connection = dataSource.getConnection();
			connection.setAutoCommit(false);
			ps = connection.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			int influence = ps.executeUpdate();
			connection.commit();
			return influence;
		} catch (SQLException e) {
			this.error("Execute Sql[{}] Error: {}", sql, e);
			try {
				connection.rollback();
			} catch (SQLException ex) {
			}
			return 0;
		} finally {
			close(connection, ps, null);
		}

	}

	/**
	 * 执行查询
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	protected List<Map<String, Object>> executeQuery(final String sql, final Object... params) {
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Map<String, Object>> list = null;
		try {
			connection = dataSource.getConnection();
			ps = connection.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			rs = ps.executeQuery();
			ResultSetMetaData md = rs.getMetaData();
			int columnCount = md.getColumnCount();
			list = new ArrayList<>();
			while (rs.next()) {
				Map<String, Object> rowData = Maps.newHashMap();
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(md.getColumnName(i), rs.getObject(i));
				}
				list.add(rowData);
			}
		} catch (SQLException e) {
			this.error("Execute Sql[{}] Error: {}", sql, e);
		} finally {
			close(connection, ps, rs);
		}
		return list;
	}

	/**
	 * 执行查询
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	protected Page<Map<String, Object>> executePage(final String sql, QueryCondition query, final Parameters param) {
		int pageNum = param.getPageIndex();
		int pageSize = param.getPageSize();
		Integer count = 0;
		List<Map<String, Object>> lst;
		Connection connection = null;
		Page<Map<String, Object>> page = new Page<>();
		try {

			// open connection
			connection = dataSource.getConnection();

			// query sql 使用 ? 占位
			String querySql = sql;

			String sqlSegment = query.getQuerySqlSegmentUseOccupied();
			if (StringUtils.isNotBlank(sqlSegment)) {
				querySql = new StringBuilder().append(sql).append(CurdDefine.SPACE).append(CurdDefine.WHERE)
						.append(CurdDefine.SPACE).append(sqlSegment).toString();
			}

			// 查询条件
			Collection<Object> params = query.getParamNameValuePairs().values();

			// 不需要分页
			if (pageNum == Parameters.NO_PAGINATION || pageSize == Parameters.NO_PAGINATION) {
				lst = this.executePage_list(connection, new StringBuilder().append(sql).append(CurdDefine.SPACE)
						.append(query.getOrderByClause()).toString(), params);
			}

			// 需要分页
			else {
				count = this.executePage_count(connection, querySql, params);
				count = count == null ? 0 : count;
				param.setRecordCount(count);
				if (count == 0) {
					lst = Lists.newArrayList();
				} else {
					int pageCount = param.getPageCount();
					if (pageNum > pageCount) {
						pageNum = pageCount;
					}
					lst = this.executePage_page(connection,
							new StringBuilder().append(querySql).append(CurdDefine.SPACE).append(CurdDefine.ORDER_BY)
									.append(CurdDefine.SPACE).append(query.getOrderByClause()).toString(),
							(pageNum - 1) * pageSize, pageSize, params);
				}
			}
			page.setData(lst).setParam(param);
		} catch (SQLException e) {
			this.error("Execute Sql[{}] Error: {}", sql, e);
		} finally {
			close(connection, null, null);
		}
		return page;
	}

	private Integer executePage_count(Connection connection, final String sql, final Collection<Object> params) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Integer total = 0;
		String countSql = StringUtils.format("SELECT COUNT(1) C FROM (%s) AS A", sql);
		try {
			ps = connection.prepareStatement(countSql);
			if (params != null) {
				int pos = 1;
				for (Object param : params) {
					ps.setObject(pos++, param);
				}
			}
			rs = ps.executeQuery();
			if (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			this.error("Execute Sql[{}] Error: {}", countSql, e);
		} finally {
			close(ps, rs);
		}
		return total;
	}

	private List<Map<String, Object>> executePage_page(Connection connection, final String sql, int offset, int limit,
			final Collection<Object> params) {
		List<Map<String, Object>> list = null;
		try {
			Dialect dialect = Dialect.getCachedDialect(connection);
			String pageSql = dialect.getLimitString(sql, offset, limit);
			list = executePage_list(connection, pageSql, params);
		} catch (SQLException e) {
			this.error("Execute Sql[{}] Error: {}", sql, e);
		}
		return list;
	}

	protected List<Map<String, Object>> executePage_list(final Connection connection, final String sql,
			final Collection<Object> params) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Map<String, Object>> list = null;
		try {
			ps = connection.prepareStatement(sql);
			if (params != null) {
				int pos = 1;
				for (Object param : params) {
					ps.setObject(pos++, param);
				}
			}
			rs = ps.executeQuery();
			ResultSetMetaData md = rs.getMetaData();
			int columnCount = md.getColumnCount();
			list = new ArrayList<>();
			while (rs.next()) {
				Map<String, Object> rowData = Maps.newHashMap();
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(md.getColumnName(i), rs.getObject(i));
				}
				list.add(rowData);
			}
		} catch (SQLException e) {
			this.error("Execute Sql[{}] Error: {}", sql, e);
		} finally {
			close(connection, ps, rs);
		}
		return list;
	}

	protected void close(final Connection con, final PreparedStatement ps, final ResultSet rs) {
		this.close(con);
		this.close(ps, rs);
	}

	protected void close(final Connection con) {
		try {
			if (con != null) {
				con.close();
			}
		} catch (Exception e) {
			this.error(e);
		}
	}

	protected void close(final Statement ps, final ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (Exception e) {
			this.error(e);
		}
		try {
			if (ps != null) {
				ps.close();
			}
		} catch (Exception e) {
			this.error(e);
		}
	}

	protected void error(Throwable e) {
		logger.error(e.getMessage());
	}

	protected void error(String format, String sql, Throwable e) {
		logger.error(format, sql, e);
	}
}